import xlwt

import pandas as pd

book = xlwt.Workbook(encoding='utf-8', style_compression=0)
sheet = book.add_sheet('sheet1', cell_overwrite_ok=True)
col = ("Year", 'Language', 'count1', 'count2', 'count3', 'count4', 'count5')
for i in range(0, 7):
    sheet.write(0, i, col[i])
df = pd.read_excel(r"C:\Users\zizifreya\PycharmProjects\plotytest\giteedata.xlsx", usecols=[1, 3, 4, 5, 6, 9])
lan = ["Ruby", "Java", "PHP", "JavaScript", "C#", "Go", "C++",
       "Android", "Objective-C", "C", "Scala", "Python", "VimL"]
T = [pd.Timestamp("2013-1-1"), pd.Timestamp("2013-2-1"), pd.Timestamp("2013-3-1"), pd.Timestamp("2013-4-1"),
     pd.Timestamp("2013-5-1"), pd.Timestamp("2013-6-1"), pd.Timestamp("2013-7-1"), pd.Timestamp("2013-8-1"),
     pd.Timestamp("2013-9-1"), pd.Timestamp("2013-10-1"), pd.Timestamp("2013-11-1"), pd.Timestamp("2013-12-1"),

     pd.Timestamp("2014-1-1"), pd.Timestamp("2014-2-1"), pd.Timestamp("2014-3-1"), pd.Timestamp("2014-4-1"),
     pd.Timestamp("2014-5-1"), pd.Timestamp("2014-6-1"), pd.Timestamp("2014-7-1"), pd.Timestamp("2014-8-1"),
     pd.Timestamp("2014-9-1"), pd.Timestamp("2014-10-1"), pd.Timestamp("2014-11-1"), pd.Timestamp("2014-12-1"),

     pd.Timestamp("2015-1-1"), pd.Timestamp("2015-2-1"), pd.Timestamp("2015-3-1"), pd.Timestamp("2015-4-1"),
     pd.Timestamp("2015-5-1"), pd.Timestamp("2015-6-1"), pd.Timestamp("2015-7-1"), pd.Timestamp("2015-8-1"),
     pd.Timestamp("2015-9-1"), pd.Timestamp("2015-10-1"), pd.Timestamp("2015-11-1"), pd.Timestamp("2015-12-1"),

     pd.Timestamp("2016-1-1"), pd.Timestamp("2016-2-1"), pd.Timestamp("2016-3-1"), pd.Timestamp("2016-4-1"),
     pd.Timestamp("2016-5-1"), pd.Timestamp("2016-6-1"), pd.Timestamp("2016-7-1"), pd.Timestamp("2016-8-1"),
     pd.Timestamp("2016-9-1"), pd.Timestamp("2016-10-1"), pd.Timestamp("2016-11-1"), pd.Timestamp("2016-12-1"),

     pd.Timestamp("2017-1-1"), pd.Timestamp("2017-2-1"), pd.Timestamp("2017-3-1"), pd.Timestamp("2017-4-1"),
     pd.Timestamp("2017-5-1"), pd.Timestamp("2017-6-1"), pd.Timestamp("2017-7-1"), pd.Timestamp("2017-8-1"),
     pd.Timestamp("2017-9-1"), pd.Timestamp("2017-10-1"), pd.Timestamp("2017-11-1"), pd.Timestamp("2017-12-1"),

     pd.Timestamp("2018-1-1"), pd.Timestamp("2018-2-1"), pd.Timestamp("2018-3-1"), pd.Timestamp("2018-4-1"),
     pd.Timestamp("2018-5-1"), pd.Timestamp("2018-6-1"), pd.Timestamp("2018-7-1"), pd.Timestamp("2018-8-1"),
     pd.Timestamp("2018-9-1"), pd.Timestamp("2018-10-1"), pd.Timestamp("2018-11-1"), pd.Timestamp("2018-12-1"),

     pd.Timestamp("2019-1-1"), pd.Timestamp("2019-2-1"), pd.Timestamp("2019-3-1"), pd.Timestamp("2019-4-1"),
     pd.Timestamp("2019-5-1"), pd.Timestamp("2019-6-1"), pd.Timestamp("2019-7-1"), pd.Timestamp("2019-8-1"),
     pd.Timestamp("2019-9-1"), pd.Timestamp("2019-10-1"), pd.Timestamp("2019-11-1"), pd.Timestamp("2019-12-1"),

     pd.Timestamp("2020-1-1"), pd.Timestamp("2020-2-1"), pd.Timestamp("2020-3-1"), pd.Timestamp("2020-4-1"),
     pd.Timestamp("2020-5-1"), pd.Timestamp("2020-6-1"), pd.Timestamp("2020-7-1"), pd.Timestamp("2020-8-1"),
     pd.Timestamp("2020-9-1"), pd.Timestamp("2020-10-1"), pd.Timestamp("2020-11-1"), pd.Timestamp("2020-12-1"),

     pd.Timestamp("2021-1-1"), pd.Timestamp("2021-2-1"), pd.Timestamp("2021-3-1"), pd.Timestamp("2021-4-1"),
     pd.Timestamp("2021-5-1"), pd.Timestamp("2021-6-1"), pd.Timestamp("2021-7-1"), pd.Timestamp("2021-8-1"),
     pd.Timestamp("2021-9-1"), pd.Timestamp("2021-10-1"), pd.Timestamp("2021-11-1"), pd.Timestamp("2021-12-1"),

     pd.Timestamp("2022-1-1"), pd.Timestamp("2022-2-1"), pd.Timestamp("2022-3-1"), pd.Timestamp("2022-4-1"),
     pd.Timestamp("2022-5-1"), pd.Timestamp("2022-6-1"), pd.Timestamp("2022-7-1"), pd.Timestamp("2022-8-1"),
     pd.Timestamp("2022-9-1"), pd.Timestamp("2022-10-1"), pd.Timestamp("2022-11-1"), pd.Timestamp("2022-12-1"),
     ]

count = 0
for item in lan:
    print(item)
    l0 = [0] * 120
    l1 = [0] * 120
    l2 = [0] * 120
    l3 = [0] * 120
    l4 = [0] * 120
    for i in range(1, df.shape[0]):
        data = df.loc[i].values
        if data[0] == item:
            for j in range(0, 120):
                if data[5] <= T[j]:
                    l0[j] = l0[j] + 1
                    l1[j] = l1[j] + data[1]
                    l2[j] = l2[j] + data[2]
                    l4[j] = l3[j] + data[3]
                    l4[j] = l4[j] + data[4]

    for i in range(1, 121):
        sheet.write(i + count * 120, 0, str(T[i - 1])[0:7])
        sheet.write(i + count * 120, 1, item)
        sheet.write(i + count * 120, 2, str(l0[i - 1]))
        sheet.write(i + count * 120, 3, str(l1[i - 1]))
        sheet.write(i + count * 120, 4, str(l2[i - 1]))
        sheet.write(i + count * 120, 5, str(l3[i - 1]))
        sheet.write(i + count * 120, 6, str(l4[i - 1]))
    count = count + 1
book.save("data.xls")
